"""
    mysql： python3 check_files.py --database_type mysql --server 127.0.0.1 --database eoss \
--username root --password 1qaz2wsx --base_directory /Users/zhaolangjing/data/documents \
--output_csv /Users/zhaolangjing/missing_files.csv --start_date 2024-03-12 --end_date 2024-08-06

    sqlserver： python check_files.py --database_type sqlserver --server your_server --database your_database --username your_username --password your_password --base_directory /path/to/your/files --output_csv missing_files.csv

"""

import pyodbc
import pymysql
import os
import pandas as pd
import argparse
from datetime import datetime


def parse_arguments():
    parser = argparse.ArgumentParser(description='Check file existence based on database records')
    parser.add_argument('--database_type', required=True, choices=['mysql', 'sqlserver'],
                        help='Type of database (mysql or sqlserver)')
    parser.add_argument('--server', required=True, help='Database server address')
    parser.add_argument('--database', required=True, help='Database name')
    parser.add_argument('--username', required=True, help='Database username')
    parser.add_argument('--password', required=True, help='Database password')
    parser.add_argument('--base_directory', required=True, help='Base directory for file paths')
    parser.add_argument('--output_csv', required=True, help='Output CSV file path for missing files')
    parser.add_argument('--start_date', required=True, help='Start date for query (YYYY-MM-DD)')
    parser.add_argument('--end_date', required=True, help='End date for query (YYYY-MM-DD)')
    return parser.parse_args()



def query_mysql_database(server, database, username, password, start_date, end_date):
    """查询 MySQL 数据库并返回结果"""
    conn = pymysql.connect(host=server, user=username, password=password, database=database)
    cursor = conn.cursor()
    query = f'''
    SELECT id, ABSOLUTEPATH, SYSFILENAME, OPERATIONCODE, OPERATIONID, PHYFILENAME FROM sys_accessories
    WHERE CREATETIME BETWEEN '{start_date}' AND '{end_date}'
    '''
    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()
    print("query_mysql_database results：", results)
    return results


def query_sqlserver_database(server, database, username, password, start_date, end_date):
    """查询 SQL Server 数据库并返回结果"""
    conn_str = (
        f"DRIVER={{ODBC Driver 17 for SQL Server}};"
        f"SERVER={server};"
        f"DATABASE={database};"
        f"UID={username};"
        f"PWD={password}"
    )
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    query = f'''
    SELECT id, ABSOLUTEPATH, SYSFILENAME, OPERATIONCODE, OPERATIONID, PHYFILENAME FROM sys_accessories
    WHERE CREATETIME BETWEEN '{start_date}' AND '{end_date}'
    '''
    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()
    print("query_sqlserver_database results：", results)
    return results


def assemble_file_paths(results, base_directory):
    """根据查询结果组装文件路径"""
    file_paths = []
    for result in results:
        absolutepath = result[1]
        sysfilename = result[2]
        filename_without_extension = os.path.splitext(sysfilename)[0]
        last_two_chars = filename_without_extension[-2:]
        file_path = os.path.join(base_directory, absolutepath, last_two_chars, sysfilename)
        file_paths.append((result, file_path))
    return file_paths


def check_files_exist(file_paths):
    """检查文件路径是否存在，并返回未找到的文件路径"""
    missing_files = []
    for result, file_path in file_paths:
        if os.path.exists(file_path):
            print(f"文件存在: {file_path}")
        else:
            print(f"文件不存在: {file_path}")
            missing_files.append((result, file_path))
    return missing_files


def save_missing_files_to_csv(missing_files, output_csv):
    """将未找到的文件路径保存到 CSV 文件"""
    if not missing_files:
        print("No missing files to save.")
        return

    data = []
    for result, file_path in missing_files:
        data.append({
            '操作代码': result[3],
            '业务ID': result[4],
            '文件ID': result[0],
            '文件名称': result[5],
            '未找到的文件路径': file_path
        })

    # 打印数据示例
    if data:
        print("Data example:", data[0])

    df = pd.DataFrame(data)

    # 打印 DataFrame 的列名和示例数据
    print("DataFrame列名：", df.columns.tolist())
    print("DataFrame 示例数据：", df.head())

    # 确保列名一致
    expected_columns = ['操作代码', '业务ID', '文件ID', '文件名称', '未找到的文件路径']
    if set(df.columns) != set(expected_columns):
        print("Column names do not match expected names.")
        return

    df.sort_values(by=['操作代码', '业务ID'], inplace=True)
    df.to_csv(output_csv, index=False, header=expected_columns)


if __name__ == '__main__':
    args = parse_arguments()

    # 根据数据库类型查询数据库
    if args.database_type == 'mysql':
        results = query_mysql_database(
            args.server, args.database, args.username, args.password, args.start_date, args.end_date
        )
    elif args.database_type == 'sqlserver':
        results = query_sqlserver_database(
            args.server, args.database, args.username, args.password, args.start_date, args.end_date
        )

    # 组装文件路径
    file_paths = assemble_file_paths(results, args.base_directory)

    # 检查文件是否存在
    missing_files = check_files_exist(file_paths)

    # 将未找到的文件路径保存到 CSV 文件
    save_missing_files_to_csv(missing_files, args.output_csv)

    print(f"Missing files saved to {args.output_csv}")


